package com.yuandian.bpm.bi.modules.user.util;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * <p>
 * Excel相关处理
 * <p>
 *
 * @author VaeTang
 * @date 2020/07/05
 * @since 1.0.0
 */
@Slf4j
public class ExcelUtil {


    private ExcelUtil() {
    }

    private static int MAX_ROW = 65535;

    /**
     * 默认样式
     */
    public static HorizontalCellStyleStrategy defaultStyle;



    static {
        /*
         * 表头
         */
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 垂直居中
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 14);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);


        /*
         * 单元格
         */
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        defaultStyle = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 多次写入 同一个对象
     * 如果大于65535，自动分sheet
     *
     * @param rowModel  导出数据对应的实体
     * @param filePath  生成文件的路径
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param <T>
     */
    public static <T> void writeExcel(Class<T> rowModel, String filePath, String sheetName, List<T> dataList) {
        ExcelWriter excelWriter = null;
        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        try {
            //先看该文件目录是否存在
            File file = new File(filePath);
            String parentPath = file.getParent();

            //目录不存在创建目录
            File directory = new File(parentPath);
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 指定文件
            excelWriter = EasyExcel.write(filePath, rowModel).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + i).build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
        } finally {
            // finish关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 不生成文件，将流写回浏览器
     *
     * @param rowModel  导出数据对应的实体
     * @param excelName excel文件名称
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param response  响应上下文
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(Class<T> rowModel, String excelName, String sheetName,
                                      List<T> dataList, HttpServletResponse response) throws IOException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        String fileName = URLEncoder.encode(excelName, "utf-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        ExcelWriter excelWriter = null;
        try {
            // 指定流
            excelWriter = EasyExcel.write(response.getOutputStream(), rowModel).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(i, sheetName + (i + 1))
                        .registerWriteHandler(defaultStyle)
                        .build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
            if (count == 0) {
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(sheetName)
                        .registerWriteHandler(defaultStyle)
                        .build();
                excelWriter.write(dataList, writeSheet);
            }
            //释放资源
            dataList.clear();

        } finally {
            // finish关闭流
            if (excelWriter != null) {
                response.getOutputStream().flush();
                excelWriter.finish();
                response.getOutputStream().close();
            }
        }
    }


    /**
     * 不生成文件，将流写回浏览器
     *
     * @param rowModel  导出数据对应的实体
     * @param excelName excel文件名称
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param writeTable 表头集合
     * @param response  响应上下文
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(Class<T> rowModel, String excelName, String sheetName,
                                      List<T> dataList, List<List<String>> writeTable, HttpServletResponse response) throws IOException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        String fileName = URLEncoder.encode(excelName, "utf-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        ExcelWriter excelWriter = null;
        try {
            // 指定流
            excelWriter = EasyExcel.write(response.getOutputStream(), rowModel).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(i, sheetName + (i + 1))
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
            if (count == 0) {
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(sheetName)
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .build();
                excelWriter.write(dataList, writeSheet);
            }

        } finally {
            // finish关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }




    /**
     * 不生成文件，将流写回浏览器
     *
     * @param rowModel  导出数据对应的实体
     * @param excelName excel文件名称
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param writeTable 表头集合
     * @param response  响应上下文
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(Class<T> rowModel, String excelName, String sheetName,
                                      List<T> dataList, List<List<String>> writeTable, Collection<String> excludeColumnFields, HttpServletResponse response) throws IOException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        String fileName = URLEncoder.encode(excelName, "utf-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        ExcelWriter excelWriter = null;

        try {
            // 指定流
            excelWriter = EasyExcel.write(response.getOutputStream(), rowModel).excludeColumnFiledNames(excludeColumnFields).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(i, sheetName + (i + 1))
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
            if (count == 0) {
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(sheetName)
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .build();
                excelWriter.write(dataList, writeSheet);
            }
            //释放资源
            dataList.clear();
        } finally {
            // finish关闭流
            if (excelWriter != null) {
                response.getOutputStream().flush();
                excelWriter.finish();
                response.getOutputStream().close();
            }
        }
    }

    /**
     * @param excelName excel文件名称
     * @param response  响应上下文
     * @throws IOException
     */
    public static void writeExcel( String excelName, HttpServletResponse response) throws IOException {
        try {
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            String fileName = URLEncoder.encode(excelName, "utf-8").replaceAll("\\+", "%20");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        } catch (Exception e) {
            log.error("response 响应上下文设置错误： " + e.getMessage());
        }
    }




    /**
     * 不生成文件，将流写回浏览器
     *
     * @param rowModel  导出数据对应的实体
     * @param excelName excel文件名称
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param writeTable 表头集合
     * @param writeTable 表头集合
     * @param columnWidth  响应上下文
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(Class<T> rowModel, String excelName, String sheetName,
                                      List<T> dataList, List<List<String>> writeTable,int columnWidth, HttpServletResponse response) throws IOException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        String fileName = URLEncoder.encode(excelName, "utf-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        ExcelWriter excelWriter = null;
        try {
            // 指定流
            excelWriter = EasyExcel.write(response.getOutputStream(), rowModel).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(i, sheetName + (i + 1))
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(columnWidth))
                        .build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
            if (count == 0) {
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(sheetName)
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(15))
                        .build();
                excelWriter.write(dataList, writeSheet);
            }

        } finally {
            // finish关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 生成文件
     *
     * @param rowModel  导出数据对应的实体
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param writeTable 表头集合
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(Class<T> rowModel,String sheetName,String filepath,
                                      List<T> dataList, List<List<String>> writeTable, Collection<String> excludeColumnFields){
        ExcelWriter excelWriter = null;
        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        try {
            //先看该文件目录是否存在
            File file = new File(filepath);
            String parentPath = file.getParent();

            //目录不存在创建目录
            File directory = new File(parentPath);
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 指定文件
            excelWriter = EasyExcel.write(filepath, rowModel).excludeColumnFiledNames(excludeColumnFields).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(i,sheetName + (i + 1))
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
        } finally {
            // finish关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }


    /**
     * 生成文件
     *
     * @param rowModel  导出数据对应的实体
     * @param sheetName sheet名称
     * @param dataList  数据集
     * @param writeTable 表头集合
     * @param <T>
     * @throws IOException
     */
    public static <T> void writeExcel(Class<T> rowModel,String sheetName,String filepath,
                                      List<T> dataList, List<List<String>> writeTable){
        ExcelWriter excelWriter = null;
        int count = dataList.size() / MAX_ROW + (dataList.size() % MAX_ROW > 0 ? 1 : 0);
        try {
            //先看该文件目录是否存在
            File file = new File(filepath);
            String parentPath = file.getParent();

            //目录不存在创建目录
            File directory = new File(parentPath);
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // 指定文件
            excelWriter = EasyExcel.write(filepath, rowModel).build();
            // 调用写入
            for (int i = 0; i < count; i++) {
                // 每次都要创建writeSheet 这里必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel
                        .writerSheet(i,sheetName + (i + 1))
                        .registerWriteHandler(defaultStyle)
                        .head(writeTable)
                        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                        .build();
                int endIndex = MAX_ROW * i + MAX_ROW;
                if (i == (count - 1)) {
                    endIndex = dataList.size();
                }
                List<T> data = dataList.subList(MAX_ROW * i, endIndex);
                excelWriter.write(data, writeSheet);
            }
        } finally {
            // finish关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }

    /**
     * 写到不同的sheet 不同的对象
     * 注意每个集合的最大数据量
     *
     * @param rowModels  要导出的对象数组
     * @param filePath   文件路径
     * @param sheetNames sheet名称数组
     * @param dataList   导出数据数组
     */
    public static void writeExcel(Class[] rowModels, String filePath, String[] sheetNames, List[] dataList) {
        ExcelWriter excelWriter = null;
        try {
            // 指定文件
            excelWriter = EasyExcel.write(filePath).build();
            // 调用写入
            for (int i = 0; i < rowModels.length; i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetNames[i]).head(rowModels[i]).build();
                excelWriter.write(dataList[i], writeSheet);
            }
        } finally {
            // 关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }


    /**
     * 读取目录文件的指定sheet, 保存并返回结果集
     *
     * @param multipartFile 上传文件
     * @param sheetNo       sheet号
     * @param headRowNumber       headRowNumber  开始读行号
     * @param rowModel      Excel数据对应的实体class
     * @param excelService  操作Excel的service
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> Map<String, List<T>> readAndSaveExcelData(
            MultipartFile multipartFile, int sheetNo, int headRowNumber, Class<T> rowModel, ExcelService<T> excelService) throws IOException {
        InputStream inputStream = getInputStream(multipartFile);
        AbstractExcelEventListener<T> excelListener = new ExcelDefaultSaveListener<>(excelService);
        readByInputStream(inputStream, sheetNo, headRowNumber, rowModel, excelListener);
        return excelListener.getResultMap();
    }

    /**
     * 读取目录文件的指定sheet, 保存并返回校验失败结果集
     *
     * @param multipartFile 上传文件
     * @param sheetNo       sheet号
     * @param headRowNumber       headRowNumber  开始读行号
     * @param rowModel      Excel数据对应的实体class
     * @param excelService  操作Excel的service
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T> readAndSaveExcel(MultipartFile multipartFile, int sheetNo, int headRowNumber, Class<T> rowModel, ExcelService<T> excelService) throws IOException {
        InputStream inputStream = getInputStream(multipartFile);
        AbstractExcelEventListener<T> excelListener = new ExcelDefaultSaveListener<>(excelService);
        readByInputStream(inputStream, sheetNo, headRowNumber, rowModel, excelListener);
        return excelListener.getFailResult();
    }


    /**
     * 根据流读取Excel内容
     *
     * @param inputStream
     * @param sheetNo
     * @param rowModel
     * @param listener
     * @param <T>
     */
    private static <T> void readByInputStream(InputStream inputStream, int sheetNo, Class<T> rowModel, AbstractExcelEventListener<T> listener) {
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo)
                .head(rowModel)
                .registerReadListener(listener)
                .build();
        ExcelReader excelReader = EasyExcel.read(inputStream).build();
        try {
            excelReader.read(readSheet);
        } finally {
            if (excelReader != null) {
                // 这里要关闭，读的时候会创建临时文件，不关闭会导致磁盘崩溃
                excelReader.finish();
            }
        }
    }

    /**
     * 根据流读取Excel内容
     *
     * @param inputStream
     * @param sheetNo
     * @param headRowNumber 表头行数
     * @param rowModel
     * @param listener
     * @param <T>
     */
    private static <T> void readByInputStream(InputStream inputStream, int sheetNo, int headRowNumber, Class<T> rowModel, AbstractExcelEventListener<T> listener) {
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo)
                .head(rowModel)
                .registerReadListener(listener)
                .build();
        ExcelReader excelReader = EasyExcel.read(inputStream).headRowNumber(headRowNumber).build();
        try {
            excelReader.read(readSheet);
        } finally {
            if (excelReader != null) {
                // 这里要关闭，读的时候会创建临时文件，不关闭会导致磁盘崩溃
                excelReader.finish();
            }
        }
    }



    /**
     * 获取输入流
     *
     * @param excel 需要解析的Excel文件
     * @return ExcelReader
     * @throws IOException
     */
    private static InputStream getInputStream(MultipartFile excel) throws IOException {
        if (excel == null) {
            throw new IOException("文件错误！");
        }
        String fileName = excel.getOriginalFilename();
        if (fileName == null) {
            throw new IOException("文件格式错误！");
        }
        if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
            throw new IOException("文件格式错误！");
        }
        return excel.getInputStream();
    }

    /**
     * 进行Excel写入操作， 可用于分页
     * @param excelWriter Excel对象
     * @param data      数据集
     * @param headList  Excel表头
     * @param excludeColumnFields   不用导出的字段
     * @param clazz     对象
     * @param sheetNo   sheet编号
     * @param SheetName     sheet名称
     * @param <T>
     */
    public static<T> void writeOnly(ExcelWriter excelWriter, List<T> data, List<List<String>> headList,
                                    List<String> excludeColumnFields, Class clazz,
                                    Integer sheetNo, String SheetName) {

        try {
            long startTime = System.currentTimeMillis();
            log.info("Excel表sheetNo {},写入数据 {} 条", sheetNo, data.size());
            ExcelWriterSheetBuilder excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
            excelWriterSheetBuilder.sheetNo(sheetNo);
            excelWriterSheetBuilder.sheetName(SheetName + sheetNo);
            excelWriterSheetBuilder.head(headList).registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                    .registerWriteHandler(ExcelUtil.defaultStyle);
            if (ObjectUtil.isNotEmpty(excludeColumnFields)) {
                excelWriterSheetBuilder.excludeColumnFiledNames(excludeColumnFields);
            }

            WriteSheet writeSheet = excelWriterSheetBuilder.build();
            writeSheet.setClazz(clazz);
            excelWriter.write(data, writeSheet);
            log.info("Excel表sheetNo {},写入耗时: {}ms", sheetNo, (System.currentTimeMillis()-startTime));
        }catch (Exception e) {
            log.error("Excel 写入失败： {}", e.getMessage(), e);
        }

    }



    /**
     * 执行更新表头
     * @param displayFields
     * @param headList
     * @param formTitleName
     */
    public static void executeUpdateHeader(List<String> displayFields,
                                           List<List<String>> headList, String formTitleName) {
        displayFields.forEach(s -> {
            List<String> childHeadList = new ArrayList<>();
            childHeadList.add(formTitleName);
            childHeadList.add(s);
            headList.add(childHeadList);
        });
    }
}
